<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
    <link rel="stylesheet" href="../node_modules/handsontable/dist/handsontable.full.css">
</head>

<body>
    <input class="ipt" type="file" />
    <div class="excel"></div>
</body>
<script src="../node_modules/js-xlsx/dist/xlsx.full.min.js"></script>
<script src="../node_modules/handsontable/dist/handsontable.full.min.js"></script>
<!-- <script src="./node_modules/xlsx-populate/browser/xlsx-populate.js"></script> -->
<script>
    let ipt = document.getElementsByClassName("ipt")[0];
    let excel = document.querySelector(".excel");

    function readFile(file) {
        return new Promise((resolve, reject) => {
            let fileReader = new FileReader();
            fileReader.readAsArrayBuffer(file);
            fileReader.onload = (e) => {
                resolve(e.target.result);
            };
            fileReader.onerror = (e) => {
                reject(e);
            };
        });
    }
    ipt.onchange = function() {
        let file = this.files[0];
        readFile(file)
            .then((res) => {
                initTable(res);
            })
            .catch((err) => {
                console.log(err);
            });
    };

    function initTable(res) {
        let workbook = XLSX.read(new Uint8Array(res), {
            type: "array",
            cellStyles: true, // 保存单元格样式/主题信息到s属性上
            raw: true,
        });
        console.log(workbook);
        let sheetName = workbook.SheetNames[0];
        let sheet = workbook.Sheets[sheetName];
        let merges = sheet["!merges"];
        console.log(XLSX.utils.sheet_to_json(sheet));

        let t = new Handsontable(excel, {
            licenseKey: "non-commercial-and-evaluation",
            data: XLSX.utils.sheet_to_json(sheet, {
                header: 1,
                defval: "",
                blankrows: true,
            }),
            // nestedHeaders: [["A11", "B11", "C11", "D11"]],
            colWidths: sheet["!cols"].map((item) => item.wpx),
            contextMenu: true,
            autoColumnSize: true,
            colHeaders: true,
            rowHeaders: true,
            manualColumnResize: true,
            manualRowResize: true,
            mergeCells: [],
            contextMenu: {
                items: {
                    row_above: {
                        name: "向上插入一行"
                    },
                    row_below: {
                        name: "向下插入一行"
                    },
                    col_left: {
                        name: "向左插入一列"
                    },
                    col_right: {
                        name: "向右插入一列"
                    },
                    remove_row: {
                        name: "删除行"
                    },
                    remove_col: {
                        name: "删除列"
                    },
                    mergeCells: {
                        name: "合并单元格"
                    },
                    copy: {
                        name: "复制"
                    },
                    cut: {
                        name: "剪切"
                    },
                },
            },
            cells(row, col, prop) {
                // console.log(row);
                let colMap = {
                    0: "A",
                    1: "B",
                    2: "C",
                    3: "D"
                };
                console.log(sheet);
                let cell = sheet[colMap[col] + (row + 1)];
                if (cell) {
                    return cell.s;
                }
            },
            //自定义渲染器
            renderer: (instance, TD, row, col, prop, value, cellProperties) => {
                if (cellProperties.fill) {
                    TD.style.background = cellProperties.fill.fgColor.rgb
                        .substring(2)
                        .replace(/^/, "#");
                    TD.style.fontSize = cellProperties.font.sz + "px";
                    if (cellProperties.font.color.rgb.length === 8) {
                        TD.style.color = cellProperties.font.color.rgb
                            .substring(2)
                            .replace(/^/, "#");
                    }
                    borderDirections = Object.keys(cellProperties.border);
                    if (borderDirections.length > 0) {
                        borderDirections.forEach((item) => {
                            try {
                                let color = cellProperties.border[item].color.rgb
                                    .substring(2)
                                    .replace(/^/, "#");
                                TD.style["border-" + item] = `1px solid ${color}`;
                            } catch (e) {}
                        });
                    }
                }
                TD.innerText = value;
            },
        });
        let MergeCellsPlugin = t.getPlugin("MergeCells");
        merges.forEach((item) => {
            MergeCellsPlugin.merge(--item.s.r, item.s.c, --item.e.r, item.e.c);
        });
    }
</script>

</html>